﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using Model;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class DPhoneuser
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string phonenumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tphoneuser");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20)
                };
            parameters[0].Value = phonenumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        public bool IMEIExists(string imei)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tphoneuser");
            strSql.Append(" where PhoneIMEI=@PhoneIMEI");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneIMEI", MySqlDbType.VarChar,40)
                };
            parameters[0].Value = imei;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.PhoneUserInfo model)
        { 
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tphoneuser(");
            strSql.Append("PhoneNumber,LoginCount,FirstLoginTime,LastLoginTime,");
            strSql.Append("type,PhoneIMEI,PhoneVersion)");
            strSql.Append(" values (");
            strSql.Append("@PhoneNumber,@LoginCount,@FirstLoginTime,@LastLoginTime,");
            strSql.Append("@type,@PhoneIMEI,@PhoneVersion)");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20),
					new MySqlParameter("@LoginCount", MySqlDbType.Int32,20),
					new MySqlParameter("@FirstLoginTime", MySqlDbType.VarChar,45),
                    new MySqlParameter("@LastLoginTime", MySqlDbType.VarChar,45),
                    new MySqlParameter("@type", MySqlDbType.VarChar,2),
                    new MySqlParameter("@PhoneIMEI", MySqlDbType.VarChar,40),
                    new MySqlParameter("@PhoneVersion", MySqlDbType.VarChar,10)};
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.LoginCount;
            parameters[2].Value = model.FirstLoginTime;
            parameters[3].Value = model.LastLoginTime;
            parameters[4].Value = model.type;
            parameters[5].Value = model.PhoneIMEI;
            parameters[6].Value = model.PhoneVersion;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.PhoneUserInfo model)
        {   // OverlayName,Overlaytype,City,Longitude,Latitude,Star,Remark
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneuser set ");
            strSql.Append("PhoneNumber=@PhoneNumber,");
            strSql.Append("LoginCount=@LoginCount,");
            strSql.Append("LastLoginTime=@LastLoginTime,");
            strSql.Append("type=@type,");
            strSql.Append("PhoneIMEI=@PhoneIMEI,");
            strSql.Append("PhoneVersion=@PhoneVersion ");
            strSql.Append("where id=@id");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar, 20),
                                              new MySqlParameter("@LoginCount", MySqlDbType.Int32, 20),
                                              new MySqlParameter("@LastLoginTime", MySqlDbType.VarChar,45),
                                              new MySqlParameter("@type", MySqlDbType.VarChar,2),
                                              new MySqlParameter("@PhoneIMEI", MySqlDbType.VarChar,40),
                                              new MySqlParameter("@PhoneVersion", MySqlDbType.VarChar,10),
                                               new MySqlParameter("@id", MySqlDbType.Int32, 20)
                                          };
            parameters[0].Value = model.PhoneNumber;
            parameters[1].Value = model.LoginCount;
            parameters[2].Value = model.LastLoginTime;
            parameters[3].Value = model.type;
            parameters[4].Value = model.PhoneIMEI;
            parameters[5].Value = model.PhoneVersion;
            parameters[6].Value = model.id;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateByIMEI(string imei, string version, string lastLonginTime)
        {   
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneuser set ");
            strSql.Append("LoginCount=LoginCount+1,");
            strSql.Append("PhoneVersion=@PhoneVersion,");
            strSql.Append("LastLoginTime=@LastLoginTime");
            strSql.Append(" where PhoneIMEI=@PhoneIMEI");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@PhoneVersion", MySqlDbType.VarChar,10),
                                              new MySqlParameter("@LastLoginTime", MySqlDbType.VarChar,45),
                                              new MySqlParameter("@PhoneIMEI", MySqlDbType.VarChar, 20)
                                          };
            parameters[0].Value = version;
            parameters[1].Value = lastLonginTime;
            parameters[2].Value = imei;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        
         public bool UpdateByNumber(string phoneNumber,int type,string lastLonginTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneuser set ");
            strSql.Append("LoginCount=LoginCount+1,");
            strSql.Append("type=@type,");
            strSql.Append("LastLoginTime=@LastLoginTime");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@type", MySqlDbType.VarChar,2),
                                              new MySqlParameter("@LastLoginTime", MySqlDbType.VarChar,45),
                                              new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar, 20)
                                          };
            parameters[0].Value = type;
            parameters[1].Value = lastLonginTime;
            parameters[2].Value = phoneNumber;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool UpdateRecommender(string phoneNumber,string recommender)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tphoneuser set ");
            strSql.Append("Recommender=@Recommender");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@Recommender", MySqlDbType.VarChar,25),
                                              new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar, 20)
                                          };

            parameters[0].Value = recommender;
            parameters[1].Value = phoneNumber;
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }            
            return false;            
        }

         
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tphoneuser ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tphoneuser ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.PhoneUserInfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,PhoneNumber,LoginCount,LastLoginTime,type ");
            strSql.Append("from tphoneuser  where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
             };
            parameters[0].Value = id;

            Model.PhoneUserInfo model = new Model.PhoneUserInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LoginCount"] != null && ds.Tables[0].Rows[0]["LoginCount"].ToString() != "")
                {
                    model.LoginCount = int.Parse(ds.Tables[0].Rows[0]["LoginCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = ds.Tables[0].Rows[0]["LastLoginTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["type"] != null && ds.Tables[0].Rows[0]["type"].ToString() != "")
                {
                    model.type = ds.Tables[0].Rows[0]["type"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public Model.PhoneUserInfo GetModelByPhoneNumber(string PhoneNumber)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,PhoneNumber,LoginCount,LastLoginTime,type,PhoneIMEI,PhoneVersion ");
            strSql.Append("from tphoneuser  where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,20)
              };
            parameters[0].Value = PhoneNumber;

            Model.PhoneUserInfo model = new Model.PhoneUserInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["LoginCount"] != null && ds.Tables[0].Rows[0]["LoginCount"].ToString() != "")
                {
                    model.LoginCount = int.Parse(ds.Tables[0].Rows[0]["LoginCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = ds.Tables[0].Rows[0]["LastLoginTime"].ToString();
                }
                if (ds.Tables[0].Rows[0]["type"] != null && ds.Tables[0].Rows[0]["type"].ToString() != "")
                {
                    model.type = ds.Tables[0].Rows[0]["type"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneIMEI"] != null && ds.Tables[0].Rows[0]["PhoneIMEI"].ToString() != "")
                {
                    model.PhoneIMEI = ds.Tables[0].Rows[0]["PhoneIMEI"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneVersion"] != null && ds.Tables[0].Rows[0]["PhoneVersion"].ToString() != "")
                {
                    model.PhoneVersion = ds.Tables[0].Rows[0]["PhoneVersion"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

 
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select  t.id,t.PhoneNumber,t.LoginCount,t.LastLoginTime,");           
            //strSql.Append("IF(d.Times IS NULL,0,d.Times) as Times");
            //strSql.Append(" From tphoneuser t left join (select count(*) ");
            //strSql.Append("as times,phoneNumber from  ttrade  where Type='1' ");
            strSql.Append("select  PhoneNumber");
            strSql.Append(" FROM tphoneuser ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("and " + strWhere);
            }
            //strSql.Append(" group by phoneNumber)d on t.PhoneNumber = d.phoneNumber");                        
            //strSql.Append(" order by LastLoginTime desc");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;
        }



        public DataSet GetList(string parameter, string parameterVaule)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  id,PhoneNumber,LoginCount,LastLoginTime");
            strSql.Append(" FROM tphoneuser ");
            strSql.Append(" where ");
            strSql.Append(parameter);
            strSql.Append(" like @A");
            MySqlParameter[] parameters = {
                                              new MySqlParameter("@A", MySqlDbType.Text)
                                          };
            parameters[0].Value = "%" + parameterVaule + "%";
            return DbHelperMySQL.Query(strSql.ToString(), parameters);
        }


         // 按规定各个时间段内登录人次统计
         public DataSet GetReportByTime(int dateType,string strWhere)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select count(*) as LoginCount,");
             strSql.Append("(select count(*) from tphoneuser) as UserCount,");
             strSql.Append("((select count(*) from tphoneuser) -count(*)) as NotLogin");
             if (dateType != 0)
             {
                 switch (dateType) 
                 {
                     case 1:  //按小时分组
                         //strSql.Append(",DATE_FORMAT(LastLoginTime, '%H' ) AS DT ");
                         strSql.Append(",DATE_FORMAT(FirstLoginTime, '%H点' ) AS DT ");
                         break;
                     case 2: //按天分组
                         //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y-%m-%d' ) AS DT ");
                         strSql.Append(",DATE_FORMAT(FirstLoginTime, '%d' ) AS DT ");
                         break;
                     case 3:  //按月分组
                         //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y-%m' ) AS DT ");
                         strSql.Append(",DATE_FORMAT(FirstLoginTime, '%m月' ) AS DT ");
                         break;
                     case 4: //按年分组
                         //strSql.Append(",DATE_FORMAT(LastLoginTime, '%Y' ) AS DT ");
                         strSql.Append(",DATE_FORMAT(FirstLoginTime, '%Y年' ) AS DT ");
                         break;
                 }
             }
             strSql.Append(" From tphoneuser ");
             if(strWhere.Trim()!="")
             {
                 strSql.Append(" where ");
                 strSql.Append(strWhere);
             }             
             strSql.Append(" group by DT order by DT asc");             
             return DbHelperMySQL.Query(strSql.ToString());
         }

        //按手机用户登录次数统计
        public DataSet GetReportByLoginCount(int[] rangeValue,string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");                         
            for (int i = 0; i < rangeValue.Length; i++)
            {
                strSql.Append("count(case when LoginCount");
                if(i==0)
                {
                    strSql.Append("<");
                    strSql.Append(rangeValue[0]);
                }
                //else if(i == rangeValue.Length -1)
                //{
                //    strSql.Append(">");
                //    strSql.Append(rangeValue[i]);
                //}
                else
                {
                    strSql.Append(">=");
                    strSql.Append(rangeValue[i - 1]);
                    strSql.Append(" and LoginCount <");
                    strSql.Append(rangeValue[i]);
                }
                strSql.Append(" then LoginCount end) as ");
                strSql.Append("A" + i);
                strSql.Append(",");
                //if (i != rangeValue.Length - 1)
                //    strSql.Append(",");

            }
            //将最后的数值-该数值以上记为一列
            strSql.Append("count(case when LoginCount");
            strSql.Append(">=");
            strSql.Append(rangeValue[rangeValue.Length - 1]);
            strSql.Append(" then LoginCount end) as ");
            strSql.Append("A" + rangeValue.Length);

            strSql.Append(" FROM tphoneuser t ");
            if(strWhere.Trim()!="")
            {
                strSql.Append("where ");
                strSql.Append(strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        // 按用户交易次数统计
        public DataSet GetReportByTradeTimes(int[] rangeValue, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(case when Times=0 then Times end) as A0,");
            for (int i = 0; i < rangeValue.Length; i++)
            {
                strSql.Append("count(case when Times");
                if (i == 0)
                {
                    strSql.Append(">0 and Times");
                    strSql.Append("<");
                    strSql.Append(rangeValue[0]);
                }                
                else
                {
                    strSql.Append(">=");
                    strSql.Append(rangeValue[i - 1]);
                    strSql.Append(" and Times <");
                    strSql.Append(rangeValue[i]);
                }
                strSql.Append(" then Times end) as ");
                strSql.Append("A" + (i+1));
                strSql.Append(",");              
            }
            //将最后的数值-该数值以上记为一列
            strSql.Append("count(case when Times ");
            strSql.Append(">=");
            strSql.Append(rangeValue[rangeValue.Length - 1]);
            strSql.Append(" then Times  end) as ");
            strSql.Append("A" + (rangeValue.Length+1));

            strSql.Append(" FROM ");
            strSql.Append("(SELECT t.PhoneNumber,t.LoginCount,t.LastLoginTime,");
            strSql.Append("IF(d.times IS NULL,0,times) as times");
            strSql.Append(" FROM tphoneuser t left join (select count(*) as times,");
            strSql.Append("phoneNumber from ttrade  where Type='1' ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("and ");
                strSql.Append(strWhere);
            }
            strSql.Append(" group by phoneNumber)d");
            strSql.Append(" on t.PhoneNumber = d.phoneNumber) TT");
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public int GetCount(string parameter, string parameterValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) from tphoneuser ");
            if(parameter.Trim()!="" && parameterValue.Trim()!="")
            {
                strSql.Append("where ");
                strSql.Append(parameter);
                strSql.Append(" like '%");
                strSql.Append(parameterValue);
                strSql.Append("%'");
            }
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if(ds==null || ds.Tables.Count !=1 || ds.Tables[0].Rows.Count!=1)
            {
                return -1;
            }
            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }

        public DataSet GetListByPage(int page, int pagesize, string parameter,string parameterValue)
        {           
            if (parameterValue == null)
            {
                return null;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  t.id,t.PhoneNumber,");
            strSql.Append("(case t.type when '0' then '安卓系统' ");
            strSql.Append("when '1' then 'IOS系统' end ) as type,");
            strSql.Append("t.PhoneIMEI,t.PhoneVersion,t.FirstLoginTime,");
            strSql.Append("t.LastLoginTime,t.LoginCount,");
            strSql.Append("IF(d.Times IS NULL,0,d.Times) as Times,t.Recommender");
            strSql.Append(" From tphoneuser t left join (select count(*) ");
            strSql.Append("as times,phoneNumber from  ttrade  where Type='1' ");      
            strSql.Append("group by phoneNumber)d on t.PhoneNumber = d.phoneNumber ");
            if(parameter.Trim()!="" && parameterValue!="")
            {
                strSql.Append("where t.");
                strSql.Append(parameter);
                strSql.Append(" like '%");
                strSql.Append(parameterValue);
                strSql.Append("%'");
            }
            strSql.Append(" order by LastLoginTime desc ");
            strSql.Append("limit ");
            strSql.Append((page-1)*pagesize);
            strSql.Append(",");
            strSql.Append(pagesize);
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            return ds;

        }

        // 按推荐人统计
        public DataSet GetReportByRecommender(int[] rangeValue, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            for (int i = 0; i < rangeValue.Length; i++)
            {
                strSql.Append("count(case when usercount");
                if (i == 0)
                {
                    strSql.Append(">0 and usercount");
                    strSql.Append("<");
                    strSql.Append(rangeValue[0]);
                }
                else
                {
                    strSql.Append(">=");
                    strSql.Append(rangeValue[i - 1]);
                    strSql.Append(" and usercount <");
                    strSql.Append(rangeValue[i]);
                }
                strSql.Append(" then usercount end) as ");
                strSql.Append("A" + (i + 1));
                strSql.Append(",");
            }
            //将最后的数值-该数值以上记为一列
            strSql.Append("count(case when usercount ");
            strSql.Append(">=");
            strSql.Append(rangeValue[rangeValue.Length - 1]);
            strSql.Append(" then usercount  end) as ");
            strSql.Append("A" + (rangeValue.Length + 1));
            strSql.Append(",(select count(*) FROM tphoneuser where Recommender is null)");
            strSql.Append(" as NoRec ");
            strSql.Append("FROM ");
            strSql.Append("(SELECT count(*) as usercount,Recommender FROM tphoneuser t ");
            strSql.Append("where Recommender is not null ");        
            if (strWhere.Trim() != "")
            {
                strSql.Append("and ");
                strSql.Append(strWhere);
            }
            strSql.Append(" group by Recommender) as t1");        
            return DbHelperMySQL.Query(strSql.ToString());
        }

        public string GetUserAreaByPhoneNumber(string phonenumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select MobileArea from tmobilearea where ");
            strSql.Append("MobileNumber = '");
            strSql.Append(phonenumber);
            strSql.Append("'");
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds==null||ds.Tables.Count==0||ds.Tables[0].Rows.Count==0)
            {
                return "";
            }           
            return ds.Tables[0].Rows[0][0].ToString();           
        }
        
        public DataSet GetListWhere(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  * FROM tphoneuser ");            
            if (strWhere!="")
            {
                strSql.Append(" where ");
                strSql.Append(strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }
    }
}
